Structured Query Language (SQL)

SQL comes from the database industry, and you can "INSERT," "DELETE," and "SELECT" rows in tables. It comes with a very rich syntax, and portions of "SELECT" grammar are used heavily in ArcGIS:

• Selecting attributes
• Raster calculator
• Geodatabases

Select by Attributes

SQL is used in a variety of ways. In the "Select by Attributes" tool, the following is used:

• SELECT * FROM TableName WHERE:
  • "WHERE Clause" or Filters
• An example would be searching for countries with the sovereignty of "United Kingdom" in the Natural Earth countries boundary shapefile. The where clause for this would simply be:
  • "SOVEREIGNT" = 'United Kingdom'

Most of the SQL statement is provided by ArcGIS while the WHERE clause is added by the user. This tool helps you to write the WHERE Clause.

Attributes in ArcGIS

Below is an example of an attribute table from a layer for the countries of the world.

SQL Comparisons

WHERE clauses can contain a number of different types of comparisons. The features that contain attributes that make the comparison true will be selected while those that make it false are not selected.

• Equals: =
• Greater than: >
• Less than: <
• Greater than or equal: >=
• Less than or equal: <=
• Not equal: <>
• Like: case independent string comparison with wild cards (%)

Filter Examples

Here are some examples of WHERE clauses for a layer of lakes in the United States.

• Where:
  • "FID" = 1
  • "Area" < 10000
  • "Area" <= 10000
  • "Name" = 'Crater Lake' (case dependent)
• Notice:
  • Fields have double quotes
  • String values have single quotes

Case Dependence

If you use the equal sign ("=") to compare two strings, the string have to be exactly the same. There are different approaches to how to deal with this in different database applications. In ArcGIS, you can use the "UPPER()" function to make all attribute values upper case and then compare them:

• UPPER("Name")='CRATER LAKE'

See the ArcGIS documentation for more examples.

Boolean Values

Boolean values are values that can be true or false. The can be represented by:

• True = T = 1
• False = F = 0

Boolean Operators

Boolean values can be combined together to make more complex statements using AND, OR, and NOT. It works like this:

• AND: If one Boolean value is true AND a second one is true, then the result is true, otherwise, the result is false.
• OR: If one Boolean value is true OR a second value is true then the result is true, otherwise, the result is false.
• NOT: If a Boolean value is true then the result is NOT true (false). If a Boolean value is false then the result is NOT false (true).

The table below shows the results of all possible combination of boolean operator results.

A	B	A AND B	A OR B	NOT A	NOT B
T	T	T	T	F	F
T	F	F	T	F	T
F	T	F	T	T	F
F	F	F	F	T	T

The comparisons and Boolean operators can now be combined together into more complex WHERE clauses.

And	"Area" > 10000 AND "IMPR" = 0 "Name" LIKE 'Hawaii' AND Area < 10000 "Species" LIKE 'Ponderosa' AND DBH > 1
Or	"RAINFALL" < 20 OR "SLOPE" > 35
Not	NOT ("STATE_NAME"='Colorado')

Order Matters:
(A AND B) OR C is not the same as A AND (B OR C)!
And - Must meet both criteria.
Or - Can meet either criteria.

If you are confused on the ordering, add parenthesis to make it clear

Test Your Knowledge

 

Additional Resources

< Back Next >